package com.ninelock.api.service;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ninelock.api.entity.ColumnInfoEntity;
import com.ninelock.api.entity.Params;
import com.ninelock.api.mapper.ColumnInfoMapper;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @ClassName: SqlToExcelService
 * @Author wultn
 * @Date 2020/10/30 10:47
 */
@Service
public class SqlToExcelService extends ServiceImpl<ColumnInfoMapper, Params> {

  @Value("${database}")
  private String tableSchema;

  @Value("${filePath}")
  private String filePath;

  public void SqlToExcel () {
    // 创建一个excel文件
    // 生成xlsx的Excel
    Workbook workbook = new SXSSFWorkbook();

    // 如需生成xls的Excel，请使用下面的工作簿对象，注意后续输出时文件后缀名也需更改为xls
    //Workbook workbook = new HSSFWorkbook();

    // 生成Sheet表
    Sheet sheet = workbook.createSheet();
    // 设置列头宽度
    for (int i=0; i<4; i++) {
      sheet.setColumnWidth(i, 4000);
    }
    // 备注行宽度长一些
    sheet.setColumnWidth(4, 10000);
    // 设置默认行高
    sheet.setDefaultRowHeight((short) 400);

    // 构建单元格样式
    CellStyle tableNameCellStyle = buildTableNameCellStyle(sheet.getWorkbook());
    CellStyle headerCellStyle = buildHeaderCellStyle(sheet.getWorkbook());
    CellStyle cellStyle = buildCellStyle(sheet.getWorkbook());

    AtomicInteger rowNum = new AtomicInteger(0);

    Params params = new Params();
    params.setTableSchema(tableSchema);
    // 获取当前数据库全部表名
    List<String> tableNames = this.baseMapper.getTableNames(params);
    System.out.println("=========================================================");
    if (CollectionUtils.isNotEmpty(tableNames)) {
      tableNames.forEach((tableName) -> {
        // 获取该表的字段信息
        params.setTableName(tableName);
        List<ColumnInfoEntity> columnInfos = this.baseMapper.getColumnInfo(params);
        // 表名信息
        CellRangeAddress region = new CellRangeAddress(rowNum.get(), rowNum.get(), 0, 4);
        sheet.addMergedRegion(region);
        Row tableNameRow = sheet.createRow(rowNum.get());
        Cell cell = tableNameRow.createCell(0);
        cell.setCellValue(tableName);
        cell.setCellStyle(tableNameCellStyle);

        // 表头信息
        rowNum.set(rowNum.get() + 1);
        Row headerRow = sheet.createRow(rowNum.get());
        convertHeaderToRow(headerRow, headerCellStyle);

        // 字段信息
        rowNum.set(rowNum.get() + 1);
        exportData(sheet, rowNum.get(), columnInfos, cellStyle);
        rowNum.set(rowNum.get() + columnInfos.size() + 1);
      });
    }

    // 以文件的形式输出工作簿对象
    FileOutputStream fileOut = null;
    try {
      String exportFilePath = filePath + "sql.xlsx";
      File exportFile = new File(exportFilePath);
      if (!exportFile.exists()) {
        exportFile.createNewFile();
      }

      fileOut = new FileOutputStream(exportFilePath);
      workbook.write(fileOut);
      fileOut.flush();
    } catch (Exception e) {
      System.out.println("输出Excel时发生错误，错误原因：" + e.getMessage());
    } finally {
      try {
        if (null != fileOut) {
          fileOut.close();
        }
        workbook.close();
      } catch (IOException e) {
        System.out.println("关闭输出流时发生错误，错误原因：" + e.getMessage());
      }
    }
  }

  private void exportData(Sheet sheet, int rowNum, List<ColumnInfoEntity> dataList, CellStyle cellStyle){
    //构建每行的数据内容
    for (ColumnInfoEntity data : dataList) {
      if (data == null) {
        continue;
      }
      //输出行数据
      Row row = sheet.createRow(rowNum++);
      convertDataToRow(data, row, cellStyle);
    }
  }

  /**
   * @Author wultn
   * @Description 设置表名样式
   * @Date 17:04 2020/10/30
   * @Param [workbook]
   * @return org.apache.poi.ss.usermodel.CellStyle
   **/
  private CellStyle buildTableNameCellStyle(Workbook workbook) {
    CellStyle style = workbook.createCellStyle();
    //对齐方式设置
    style.setAlignment(HorizontalAlignment.CENTER);
    //设置背景颜色
    style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    //粗体字设置
    Font font = workbook.createFont();
    font.setBold(true);
    style.setFont(font);
    return style;
  }

  /**
   * 设置第一行列头的样式
   * @param workbook 工作簿对象
   * @return 单元格样式对象
   */
  private CellStyle buildHeaderCellStyle(Workbook workbook) {
    CellStyle style = workbook.createCellStyle();
    //对齐方式设置
    style.setAlignment(HorizontalAlignment.LEFT);
    //边框颜色和宽度设置
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框
    style.setBorderLeft(BorderStyle.THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框
    style.setBorderTop(BorderStyle.THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框
    //设置背景颜色
    style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    //粗体字设置
    Font font = workbook.createFont();
    font.setBold(true);
    style.setFont(font);
    return style;
  }

  /**
   * @Author wultn
   * @Description 设置数据样式+
   * @Date 16:41 2020/10/30
   * @Param [workbook]
   * @return org.apache.poi.ss.usermodel.CellStyle
   **/
  private CellStyle buildCellStyle(Workbook workbook) {
    CellStyle style = workbook.createCellStyle();
    //对齐方式设置
    style.setAlignment(HorizontalAlignment.LEFT);
    //边框颜色和宽度设置
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框
    style.setBorderLeft(BorderStyle.THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框
    style.setBorderTop(BorderStyle.THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框
    return style;
  }

  /**
   * @Author wultn
   * @Description 将header转成行
   * @Date 16:47 2020/10/30
   * @Param [data, row, cellStyle]
   * @return void
   **/
  private void convertHeaderToRow(Row row, CellStyle headerCellStyle){
    int cellNum = 0;
    Cell cell;
    cell = row.createCell(cellNum++);
    cell.setCellValue("字段名");
    cell.setCellStyle(headerCellStyle);
    cell = row.createCell(cellNum++);
    cell.setCellValue("字段类型");
    cell.setCellStyle(headerCellStyle);
    cell = row.createCell(cellNum++);
    cell.setCellValue("是否为空");
    cell.setCellStyle(headerCellStyle);
    cell = row.createCell(cellNum++);
    cell.setCellValue("默认值");
    cell.setCellStyle(headerCellStyle);
    cell = row.createCell(cellNum);
    cell.setCellValue("备注");
    cell.setCellStyle(headerCellStyle);
  }

  /**
   * 将数据转换成行
   * @param data 源数据
   * @param row 行对象
   */
  private void convertDataToRow(ColumnInfoEntity data, Row row, CellStyle cellStyle){
    int cellNum = 0;
    Cell cell;
    cell = row.createCell(cellNum++);
    cell.setCellValue(null == data.getColumnName() ? "" : data.getColumnName());
    cell.setCellStyle(cellStyle);
    cell = row.createCell(cellNum++);
    cell.setCellValue(null == data.getColumnType() ? "" : data.getColumnType());
    cell.setCellStyle(cellStyle);
    cell = row.createCell(cellNum++);
    cell.setCellValue(null == data.getIsNullable() ? "" : data.getIsNullable());
    cell.setCellStyle(cellStyle);
    cell = row.createCell(cellNum++);
    cell.setCellValue(null == data.getColumnDefault() ? "" : data.getColumnDefault());
    cell.setCellStyle(cellStyle);
    cell = row.createCell(cellNum);
    cell.setCellValue(null == data.getColumnComment() ? "" : data.getColumnComment());
    cell.setCellStyle(cellStyle);
  }
}
